package com.dao.imp;

import com.bean.User;
import com.dao.BaseUserDao;
import com.exception.idNumberException;
import com.util.DateFormatUtil;
import com.util.DruidUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @Author: 柏竹
 * @Description: 用户业务 库操作
 * @Date_Created_in: 2021-05-28 11:13
 * @Modified_By: Sans
 * @Project: 快递e栈
 */
public class UserDaoMysql extends DruidUtil implements BaseUserDao {
    
    
    // 查 用户 总量、新增
    public static final String SQL_CONSOLE =
            "SELECT COUNT(ID) data_Size,COUNT(TO_DAYS(CREATETIME)=TO_DAYS(NOW()) OR NULL) data_Day FROM USER ";
    // 查 用户 所有数据
    public static final String SQL_FIND_ALL = "SELECT * FROM USER";
    // 查 用户 分页数据
    public static final String SQL_FIND_LIMIT = "SELECT * FROM USER LIMIT ? , ? ";
    // 查 用户 指定 ID
    public static final String SQL_FIND_BY_ID = "SELECT * FROM USER WHERE ID = ? ";
    // 查 用户 指定 身份证
    public static final String SQL_FIND_BY_IDNUMBER = "SELECT * FROM USER WHERE IDNUMBER = ? ";
    // 查 用户 指定 名称
    public static final String SQL_FIND_BY_USERNAME = "SELECT * FROM USER WHERE USERNAME = ? ";
    // 查 用户 指定 电话
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM USER WHERE USERPHONE = ? ";
    
    // 用户 新增
    public static final String SQL_INSERT =
            "INSERT INTO USER(USERNAME,USERPHONE,PASSWORD,CREATETIME,IDNUMBER,LOGINIP) VALUES(?,?,?,NOW(),?,?)";
    // 用户 修改
    public static final String SQL_UPDATE = "UPDATE USER SET USERNAME=?,USERPHONE=?,PASSWORD=?,IDNUMBER=? WHERE ID = ? ";
    // 用户 删除
    public static final String SQL_DELETE = "DELETE FROM USER WHERE ID=?";
    
    // 用户 登录账号
    public static final String SQL_LOGIN = "SELECT id FROM user WHERE username=? AND `password`=?";
    // 用户 登录记录
    public static final String SQL_LOGIN_LOG = "UPDATE user SET loginime=?,loginip=?,status=1 WHERE username=?";
    // 用户 退出记录
    public static final String SQL_LOGOUT = "UPDATE user SET status=0 WHERE id = ? ";
    
    /**
     * 查 库 全部用户(总数,新增)
     *
     * @return {size:总数,day:新增}
     */
    @Override
    public Map<String, Integer> console() {
        ResultSet rs = query(SQL_CONSOLE , null);
        Map<String , Integer> map = new HashMap<>();
        try {
            if (rs.next()){
                map.put("data_Size",rs.getInt("data_Size"));
                map.put("data_Day",rs.getInt("data_Day"));
            }else{
                return null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                closeDaoAll();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return map;
    }
    
    /**
     * 查 库 全部用户
     *
     * @param limit
     * @param offset
     * @param pageNumber
     * @return {size:总数,day:新增}
     */
    @Override
    public List<User> findAll(boolean limit , int offset , int pageNumber) {
        List<User> UsersList = null;
        if (limit){
            List list = new ArrayList();
            list.add(offset);
            list.add(pageNumber);
            query(SQL_FIND_LIMIT,list);
        }else{
            query(SQL_FIND_ALL,null);
        }
        try {
            UsersList = refexClass(User.class);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return UsersList;
    }
    
    /**
     * 查 库 指定用户
     *
     * @param type
     * @param data
     * @return 查询用户
     */
    @Override
    public List<User> findUser(String type , String data) {
        String sql;
        List list = new ArrayList();
        List<User> UsersList = null;
        switch (type){
            case "id":
                sql = SQL_FIND_BY_ID;
                list.add(Integer.parseInt(data));
                break;
            case "idNumber":
                sql = SQL_FIND_BY_IDNUMBER;
                list.add(data);
                break;
            case "username":
                sql = SQL_FIND_BY_USERNAME;
                list.add(data);
                break;
            case "userphone":
                sql = SQL_FIND_BY_USERPHONE;
                list.add(data);
                break;
            default:
                return null;
        }
        query(sql,list);
        try {
            UsersList = refexClass(User.class);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return UsersList;
    }
    
    /**
     * 用户 录入
     *
     * @param user
     * @return 是否成功
     * SQL_INSERT : INSERT INTO USER(USERNAME,USERPHONE,PASSWORD,CREATETIME,IDNUMBER,LOGINIP) VALUES(？,？,？,NOW(),？，？)
     */
    @Override
    public boolean insert(User user) throws idNumberException {
        List list = new ArrayList();
        boolean bool = false;
        list.add(user.getUsername());
        list.add(user.getUserphone());
        list.add(user.getPassword());
        list.add(user.getIdnumber());
        list.add(user.getIp());
        try {
            bool = update(SQL_INSERT,list)>0;
        } catch (SQLException throwables) {
            //for key 'user.idnumber'
            String info = throwables.getMessage();
            if (info.endsWith("for key 'user.idnumber'")) {
                idNumberException idNumberException = new idNumberException(info);
                throw idNumberException;
            }
        }finally {
            closeDaoAll();
        }
        return bool;
    }
    
    /**
     * 用户 修改
     *
     * @param id
     * @param user
     * @return 是否成功
     * SQL_UPDATE : UPDATE USER SET USERNAME=?,USERPHONE=?,PASSWORD=?,IDNUMBER=? WHERE ID = ?
     */
    @Override
    public boolean update(int id , User user) {
        List list = new ArrayList();
        boolean bool = false;
        list.add(user.getUsername());
        list.add(user.getUserphone());
        list.add(user.getPassword());
        list.add(user.getIdnumber());
        list.add(id);
        try {
            bool = update(SQL_UPDATE,list)>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return bool;
    }
    
    /**
     * 用户 删除
     *
     * @param id
     * @return 是否成功
     * SQL_DELETE : DELETE FROM USER WHERE ID=?
     */
    @Override
    public boolean delete(int id) {
        List list = new ArrayList();
        boolean bool = false;
        list.add(id);
        try {
            bool = update(SQL_DELETE,list)>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return bool;
    }
    
    /**
     * 用户 登录账号
     *
     * @param username 账号
     * @param password 密码
     * @return 是否成功
     */
    @Override
    public boolean login(String username , String password) {
        List list = new ArrayList();
        list.add(username);
        list.add(password);
        ResultSet rs = query(SQL_LOGIN , list);
        try {
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                closeDaoAll();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return false;
    }
    
    /**
     * 用户 登录记录
     *
     * @param username 账号
     * @param date     登录时间
     * @param ip       登录ip
     * @return 是否成功
     * UPDATE user SET loginime=?,loginip=?,status=1 WHERE username=?
     */
    @Override
    public Boolean login_log(String username , Date date , String ip) {
        List list = new ArrayList();
        Boolean bool = false;
        list.add(DateFormatUtil.format(date));
        list.add(ip);
        list.add(username);
        try {
            bool = update(SQL_LOGIN_LOG,list)>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return bool;
    }
    
    /**
     * 用户 退出、下线
     *
     * @param id 标号
     * @return 是否成功
     */
    @Override
    public boolean logout(String id) {
        List list = new ArrayList();
        list.add(id);
        Boolean bool = false;
        try {
            bool = update(SQL_LOGOUT,list)>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return bool;
    }
}
